package com.can;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.Date;

public class ReadCellTypeUtils {

    /**
     * 读取工具类
     * @param fileInputStream
     * @param path
     * @throws IOException
     */
    @Test
    public static void CellTypeUtils(FileInputStream fileInputStream, String path) throws IOException {
        //1、获取文件流
        fileInputStream = new FileInputStream(path);
        //2、创建一个工作簿。使用excel能操作的这边都可以操作
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //3、获取表
        Sheet sheet = workbook.getSheetAt(0);
        //3、获取标题内容
        Row rowTitle = sheet.getRow(0);
        if(rowTitle!=null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();//读取总列数
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);//得到每一列
                if(cell!=null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
            //换行
            System.out.println();
        }
        //获取内容
        int rowCount = sheet.getPhysicalNumberOfRows(); //读取总行数
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum); //读取除了第一行外的每一行
            if(rowData!=null){
                //读取列
                int cellCount = rowData.getPhysicalNumberOfCells();//读取行的总列数
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数据类型
                    if(cell!=null){
                        int cellType = cell.getCellType();
                        String cellTValue = "";
                        switch (cellType){
                            case XSSFCell.CELL_TYPE_STRING: //字符串
                                System.out.print("【STRING】");
                                cellTValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN: //布尔值
                                System.out.print("【BOOLEAN】");
                                cellTValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case XSSFCell.CELL_TYPE_BLANK: //空
                                System.out.print("【BLANK】");
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC: //数字（日期、普通数字）
                                System.out.print("【NUMERIC】");
                                if(DateUtil.isCellDateFormatted(cell)){ //日期
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellTValue = new DateTime(date).toString("yyyy-MM-dd HH:ss:dd");
                                }else{  //普通数字
                                    //如果不是日期格式，防止数字过长！
                                    System.out.print("【普通数字】");
//                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);//普通数字就转化成字符串输出
//                                    cellTValue = cell.toString();

                                    //解决科学计数法输出问题
                                    DecimalFormat df = new DecimalFormat("0");
                                    BigDecimal bd = new BigDecimal(cellTValue);
                                    cellTValue = bd.toPlainString().toString();

                                }
                                break;
                            case XSSFCell.CELL_TYPE_FORMULA: //公式
                                String formula = cell.getCellFormula();
                                System.out.print("公式【"+formula+"】");
                                //拿到计算公式 eval
                                FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                CellValue evaluate = formulaEvaluator.evaluate(cell);
                                cellTValue = evaluate.formatAsString();
                                break;
                            case XSSFCell.CELL_TYPE_ERROR: //数据类型错误
                                System.out.print("【数据类型错误】");
                                break;
                            default:
                                System.out.print("【未知类型】");
                                break;
                        }
                        System.out.println(cellTValue);
                    }
                }
            }
        }
        //关闭流
        fileInputStream.close();
    }
}
